<?php


namespace app\modules\admin\models;

use Yii;
use yii\web\IdentityInterface;
use yii\helpers\Url;
use yii\base\Exception;



/**
 * ContactForm is the model behind the contact form.
 */

class CallProcedure
{
    //链接数据库

    //$Type  1,2  增删改 查

    public static function  CallProcedure($Type,$ProcedureName, $ProcedureData)
    {
//        $ProcedureData = array(
//
//            'r_page_size' => 0,
//            'r_page_now' => 2,
//            'r_order' => ''
//
//        );
//        $ProcedureName='UserAdd';
//        $ProcedureData=array(
//            'u_name'=>1,
//            'u_pass'=>1,
//            'u_fullname'=>1,
//            'u_moblie'=>1,
//            'u_email'=>1,
//            'u_isdelete'=>0
//
//        );
        $string = implode(',:', array_keys($ProcedureData));
        $array = null;
        try {
            yii::$app->db->open();
            $con = yii::$app->db;
        //    $admininfo= $con->createCommand("CALL Tests(:r_page_size,:r_page_now,:r_order,@u_count)");
//            $admininfo->bindValue(':r_page_size', $pnums);
//            $admininfo->bindValue(':r_page_now', $page);
//            $admininfo->bindValue(':r_order', '');
//            $userVerify= $admininfo->queryAll(); //$admininfo->queryAll(); $admininfo->execute();返回执行条数 $admininfo->queryOne();返回一条数据
//            $s = $con->createCommand("select @u_count");
//            $ret = $s->queryAll();
//            var_dump($ret);exit;
            $admininfo = $con->createCommand("CALL $ProcedureName(:$string)");
            foreach ($ProcedureData as $key => $value) {
                $admininfo->bindValue(':' . $key, $value);
            }

            if ($Type==1) //查
            {
                $userVerify = $admininfo->queryAll();
            }else{
                $userVerify = $admininfo->execute();
            }
          //  $userVerify = $admininfo->execute(); //$admininfo->queryAll(); $admininfo->execute();返回执行条数 $admininfo->queryOne();返回一条数据
            $array["result"] = 1;
            $array["value"] = $userVerify;
            return $array;
        }catch (Exception $ex) {
            $array["result"] = -1;
            $array["value"] = $ex->getMessage();
            return $array;
        }
//




    }
    /**
     * @$Procedure
     * @$Procedure_name
     * @$Procedure_data_in
     * @$Procedure_data_out
     * 数据库底层公用query方法
     * 李珈 2016-04-22
     * 测试用例backmanage/login/userlogintest
     */
    public static function CallProcedure_query($Procedure)
    {
        //打开一个数据库连接
        try {
            yii::$app->db->open();
            $con = yii::$app->db;
        }
        catch (\Exception $ex) {
            $array["result"] = -1;
            $array["value"] = $ex->getMessage();
            return $array;
        }
        //定义一个参数传递的字符串
        $string="";
        //判断$Procedure["Procedure_data_in"]是否有值，并且是否是数组
        if(isset($Procedure["Procedure_data_in"]) && is_array($Procedure["Procedure_data_in"]) && count($Procedure["Procedure_data_in"])>0)
        {
            //如果有包装一个（：key1,:key2,key3）的传递字符串
            $string.="(";
            foreach($Procedure["Procedure_data_in"] as $key=>$value)
            {
                //如果key存在符号:,会破坏存贮过程调用规则
                if(strpos($key, ":"))
                {
                    $array["result"] = -2;
                    $array["value"] = "Procedure_data_in key error!!!";
                    return $array;
                }
                else
                    $string.=":".$key.",";
            }

        }
        //判断$Procedure["Procedure_data_out"]是否有值，并且是否是数组
        if(isset($Procedure["Procedure_data_out"]) && is_array($Procedure["Procedure_data_out"]) && count($Procedure["Procedure_data_out"])>0)
        {
            //如果有包装一个（：key1,:key2,key3）的传递字符串
            foreach($Procedure["Procedure_data_out"] as $key=>$value)
            {
                //如果key存在符号:,会破坏存贮过程调用规则
                if(strpos($key, "@"))
                {
                    $array["result"] = -2;
                    $array["value"] = "Procedure_data_in key error!!!";
                    return $array;
                }
                else
                    $string.="@".$key.",";
            }

        }

        if($string!="" && strlen($string)>1)
        {
            $string= substr($string,0,strlen($string)-1);
            $string.=")";
        }


        //判断$Procedure['Procedure_name']是否有值，该参数为存贮过程名字。
        if(array_key_exists("Procedure_name",$Procedure))
        {
            $Procedure_name= $Procedure['Procedure_name'];
        }
        else
        {
            //不存在存储过程名字参数的话直接返回错误
            $array["result"] = -2;
            $array["value"] = "no procedure name!!!";
            return $array;
        }
        //生成一个sql命令,在这里为call+存储过程名字+参数【可以在空】

            $admininfo = $con->createCommand("CALL $Procedure_name $string");


        if($string!="" && strlen($string)>0)
        {
            foreach ($Procedure["Procedure_data_in"] as $key => $value) {
                $admininfo->bindValue(':'.$key, $value);
            }
        }

        //获取datareader数据流
        try { $userVerify = $admininfo->query();
        }catch (Exception $ex) {
            $array["result"] = -1;
            $array["value"] = $ex->getMessage();
            return $array;
        }
        $array = array();
        do {
            //循环获取结果集var
            if($userVerify->count()>0) {
                $arrayvalue = $userVerify->readAll();
                $array[]=$arrayvalue;
            }
        } while ($userVerify->nextResult());
        $userVerify->close();
        if($array!=null  && is_array($array)){
            return $array;
        }else{
            $array["result"] = 0;
            $array["value"] = 'no data!!!';
            return $array;
        }

    }

    /**
     * @$Procedure
     * @$Procedure_name
     * @$Procedure_data_in[key=>value];key不允许出现“：”
     *数据库底层公用execute方法
     *李珈 2016-04-22
     */
    public static function  CallProcedure_execute($Procedure)
    {
        try {
            //打开一个数据库连接
            yii::$app->db->open();
            $con = yii::$app->db;
        }catch (Exception $ex) {
            //如果存在异常，返回-1，且返回异常信息
            $array["result"] = -1;
            $array["value"] = $ex->getMessage();
            return $array;
        }
            //定义一个参数传递的字符串
            $string="";
            //判断$Procedure["Procedure_data_in"]是否有值，并且是否是数组
            if(isset($Procedure["Procedure_data_in"]) && is_array($Procedure["Procedure_data_in"]) && count($Procedure["Procedure_data_in"])>0)
            {
                //如果有包装一个（：key1,:key2,key3）的传递字符串
                $string.="(";
                foreach($Procedure["Procedure_data_in"] as $key=>$value)
                {
                    //如果key存在符号:,会破坏存贮过程调用规则
                    if(strpos($key, ":"))
                    {
                        $array["result"] = -2;
                        $array["value"] = "Procedure_data_in key error!!!";
                        return $array;
                    }
                    else
                        $string.=":".$key.",";
                }
                $string= substr($string,0,strlen($string)-1);
                $string.=")";
            }


            //判断$Procedure['Procedure_name']是否有值，该参数为存贮过程名字。
            if(array_key_exists("Procedure_name",$Procedure))
            {
                $Procedure_name= $Procedure['Procedure_name'];
            }
            else
            {
                //不存在存储过程名字参数的话直接返回错误
                $array["result"] = -2;
                $array["value"] = "no procedure name!!!";
                return $array;
            }

            //生成一个sql命令,在这里为call+存储过程名字+参数【可以在空】
            $admininfo = $con->createCommand("CALL $Procedure_name $string");

            if($string!="" && strlen($string)>0)
            {
                foreach ($Procedure["Procedure_data_in"] as $key => $value) {
                    $admininfo->bindValue(':'.$key, $value);
                }
            }
            //开始数据库层的execute操作
            try {
                $userVerify = $admininfo->execute();
            }catch (Exception $ex) {
                //如果存在异常，返回-1，且返回异常信息
                $array["result"] = -1;
                $array["value"] = $ex->getMessage();
                return $array;
            }
            //execute操作返回数值为影响行数
            $array["result"]=$userVerify;
            return $array;

    }
}




